"SELECT Count(Ports.PortID) AS CountOfPortID
FROM ((Ports
RIGHT JOIN Switches ON Ports.SwitchID = Switches.SwitchID)
INNER JOIN SwitchTypes ON Switches.SwitchTypeID = SwitchTypes.SwitchTypeID)
WHERE (((Switches.SwitchID)=" & Me!SwitchID & ")
AND ( IIf(nz(InStr(1,[BBLabels],'.' & (Ports.PortLabel) & '.',1),0)>0,1,0)=1 )
AND (Ports.StatusCodeID)=" & InUseValue & ");"
which I need to use in php and postgres7.
The line:
AND ( IIf(nz(InStr(1,[BBLabels],'.' & (Ports.PortLabel) & '.',1),0)>0,1,0)=1 )
is throwing me for a loop as to how to get the same using php for postgres7.
From what I understand of it, it if saying that if the BBlabel is in the port label (or is this vice versa)
I wrote a php fn to check this (as i understand it):
Iif is non standard, look at the PostgreSQL manual for CASE (http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html) and note that string concatenation (http://www.postgresql.org/docs/8.1/interactive/functions-string.html) is performed with ||
yes, iif is non-std...
my question is more what is the purpose and how can achieve thiseffect IFF it is needed...
sorta lost...
been staring @ it for hours...
Please, review the docs, there you can find the purpose of it and of the functions I suggested.
I can only add that NZ should be equivalent to COALESCE
Thank you again,
Upon review I see the importance of those references...
I am still up in the air about the line of code though...
I did not write this, neither is the author available any more.
could you shed some light on this line of code:
AND ( IIf(nz(InStr(1,[BBLabels],'.' & (Ports.PortLabel) & '.',1),0)>0,1,0)=1 )
thank you!
ok, so I believe the string is saying:
that
if the port label is in the bblabel return the value, else 1
then if instr returns > 0 nz returns 1 (true) else 0 (false)
iff nz returns one THEN it's ok...?
so is this like:
"SELECT count(port.p_id) AS cpid
FROM ((port
RIGHT JOIN switch ON port.p_switch_id = switch.sw_id)
INNER JOIN switch_type ON switch.sw_type_id = switch_type.swt_id)
WHERE (((switch.sw_id) = ".$_SESSION['s'].")
AND (CASE COALESCE(stripos(port.p_label, switch_type.swt_backbone_label))
WHEN > 0 THEN 1
WHEN 0 THEN 0
END)
AND (port.p_status_code_id) = ".$port_active
I still don't understand the AND part though and am not sure this will work...
so WHERE ...
AND 1
AND ...
CAn you help clarify a little more...
I believe that this was over complicated and that by using LIKE I can create the same functionality.
as such:
SELECT Count(port.p_id) AS cpid
FROM (port RIGHT JOIN switch ON port.p_switch_id = switch.sw_id) INNER JOIN switch_type ON switch.sw_type_id = switch_type.swt_id
WHERE (((switch.sw_id)=1) AND ((switch_type.swt_backbone_label) Like '%.port.p_label.%'))
GROUP BY port.p_status_code_id
HAVING (((port.p_status_code_id)=10));
that is assuming that :
Like '%.port.p_label.%'
will search against ~= anything .portlabel. anything
plz let me know if I am wrong...
SELECT count(port.p_id) AS cpid FROM (port RIGHT JOIN switch ON port.p_switch_id = switch.sw_id) INNER JOIN switch_type ON switch.sw_type_id = switch_type.swt_id WHERE (((switch.sw_id)=1) AND ((switch_type.swt_backbone_label) Like '%.port.p_label.%') AND ((port.p_status_code_id)=8));
this returns the right # of rows but doesn't return it as a count...